--EXEC SP_UPDATE_DROPOUT_NOTIFICATION_LOTS
CREATE PROCEDURE SP_UPDATE_DROPOUT_NOTIFICATION_LOTS

AS 
BEGIN
	DECLARE @PART_NUMBER VARCHAR(50)
	DECLARE @lot_number VARCHAR(50)
	DECLARE @fab_Lot_Number VARCHAR(50)
	DECLARE @ps_type VARCHAR(50)
	DECLARE @qty_in       INT
	DECLARE @NAT_ID		INT
	DECLARE @sort_order INT
	DECLARE @Shipped_Lot_Count INT
	DECLARE @Wip_Lot_Count	   INT

	declare  @wipDropLotsCursor cursor
	SET @wipDropLotsCursor = CURSOR FOR SELECT NAT_ID, Part_Number, Lot_Number, Fab_Lot_Number, Process_Type, Die_Qty FROM Notification_Alert_Table
										WHERE Notification_Name = 'DropOut Lot Notification' AND IS_USER_NOTIFIED = 1 AND STATUS = 'Open'

	OPEN @wipDropLotsCursor
	FETCH NEXT FROM @wipDropLotsCursor INTO @NAT_ID, @PART_NUMBER, @lot_number,@fab_Lot_Number,@ps_type,@qty_in
	WHILE ( @@FETCH_STATUS = 0 )
	BEGIN
		
		SELECT @sort_order = SORTING_ORDER FROM PROCESS_TYPES_TABLE WHERE Process_Type = @ps_type
		
		
		SELECT @Shipped_Lot_Count = COUNT(*) FROM SHIPPED_LOT SL, LOT_TABLE LT, PROCESS_STEP PS, PROCESS_TYPES_TABLE PTT
			WHERE SL.LOT_ID = LT.LOT_ID AND LT.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND PS.PS_TYPE = PTT.PROCESS_TYPE 
				AND LT.FAB_LOT_NUMBER = @fab_Lot_Number AND PTT.SORTING_ORDER >= @sort_order

		SELECT @Wip_Lot_Count = COUNT(*) FROM LOT_TABLE LT, PROCESS_STEP PS, PROCESS_TYPES_TABLE PTT
			WHERE LT.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND PS.PS_TYPE = PTT.PROCESS_TYPE 
				AND LT.FAB_LOT_NUMBER = @fab_Lot_Number AND PTT.SORTING_ORDER > @sort_order
		/*IF @fab_Lot_Number = 'VM1838.1'
		BEGIN
				PRINT @Wip_Lot_Count
				PRINT @sort_order
				PRINT @PS_TYPE
		END*/

		IF  @Shipped_Lot_Count > 0 OR @Wip_Lot_Count > 0
		BEGIN
			
			UPDATE NOTIFICATION_ALERT_TABLE SET IS_USER_NOTIFIED = 0 , STATUS = 'Not A DropOut Lot' WHERE NAT_ID = @NAT_ID		
		END
						
		SET @PART_NUMBER	= null	
		SET @lot_number		= null
		SET @fab_Lot_Number = null
		SET @qty_in			= null
		SET @ps_type		= null
		SET @NAT_ID			= null
		SET @sort_order		= null
	FETCH NEXT FROM @wipDropLotsCursor INTO @NAT_ID, @PART_NUMBER, @lot_number,@fab_Lot_Number,@ps_type,@qty_in
	END
	CLOSE @wipDropLotsCursor
	DEALLOCATE @wipDropLotsCursor

END